{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "np.set_printoptions(suppress=True) #为了直观的显示数字，不采用科学计数法\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore') #不显示代码警告（警告不是报错）\n",
    "#设置数据源文件路径\n",
    "file = '广东中行 8791.xls' #此处为相对路径，可根据xlsx文件的实际路径更改此变\n",
    "file2 = '广东农行4276.xls' \n",
    "file3 = '广东工行 8573.xlsx'\n",
    "file4 = '广东广发 0156.xls' \n",
    "file5 = '广东建行1054.xls'\n",
    "file14='广东农商行 7295.xls'\n",
    "file6 = '湖北中行2986.xls' #此处为相对路径，可根据budget_data.xlsx文件的实际路径更改此变\n",
    "file13 = '湖北工行9666.csv' \n",
    "file12='广东农行4659.xls'\n",
    "\n",
    "df01 = pd.read_excel(file,sheet_name=0,header = 8) #导入登记表，header=8就是从第八行开始读取\n",
    "df01.rename(columns={'交易日期[ Transaction Date ]':'日期'},inplace=True) #将表头的名称改成统一的名称\n",
    "df01.rename(columns={\"付款人名称[ Payer's Name ]\":'付款人'},inplace=True)\n",
    "df01.rename(columns={\"收款人名称[ Payee's Name ]\":'收款人'},inplace=True)\n",
    "df01.rename(columns={\"交易附言[ Remark ]\":'摘要'},inplace=True)\n",
    "df01.rename(columns={\"交易金额[ Trade Amount ]\":'金额'},inplace=True)\n",
    "df01=df01[[\"日期\",\"付款人\",\"收款人\",\"摘要\",\"金额\"]]#选取需要的列，形成一个新表\n",
    "df01['对方户名']=df01['付款人'].map(str)+\"付\"+df01['收款人'].map(str)##将两列合并变成一列\n",
    "df01['日期']=pd.to_datetime(df01['日期'],format=\"%Y%m%d\",errors='coerce')#日期格式转化，这个作为索引\n",
    "df01 = df01.fillna(0)#将NAN值变成0\n",
    "df11=df01.loc[df01['金额'] >= 0]##将金额大于零的数值提取出来。\n",
    "df11.rename(columns={'金额':'收入金额'},inplace=True)#改名\n",
    "df12=df01.loc[df01['金额'] < 0]##将金额小于零的数值提取出来\n",
    "df12.rename(columns={'金额':'支出金额'},inplace=True)\n",
    "df16 = pd.merge(df11, df12, on=['日期', '对方户名','摘要'], how='outer')#合并成一行\n",
    "df16['银行']='广东中行 8791'##加上银行名称\n",
    "df16['支出金额']=df16['支出金额']*-1##负数改成正数\n",
    "df16 = df16.fillna(0)#将NAN值变成0\n",
    "df16=df16[[\"日期\",\"对方户名\",\"摘要\",\"支出金额\",\"收入金额\",\"银行\"]]#重新得出一个新表\n",
    "df02 = pd.read_excel(file2,sheet_name=0,header = 1) #导入登记表，如果加上index_col=2，就是以二级分类作为索引\n",
    "df02['日期']=pd.to_datetime(df02['会计日期'],format=\"%Y%m%d\",errors='coerce')#日期格式转化\n",
    "##df02['金额']=df02['收入金额']+df02['支出金额']*-1\n",
    "df02.rename(columns={\"交易用途\":'摘要'},inplace=True)#改列名\n",
    "df02=df02[[\"日期\",\"对方户名\",\"摘要\",\"支出金额\",\"收入金额\"]]\n",
    "df02 = df02.fillna(0)#将NAN值变成0\n",
    "df02=df02.loc[df02['摘要'] != 0]##将摘要不等于零的提取出来\n",
    "df02['银行']='广东农行4276'##增加一行银行列\n",
    "\n",
    "df03 = pd.read_excel(file3,header = 1)  #导入登记表，如果加上index_col=2，就是以二级分类作为索引 gb2312 gb18030\n",
    "df03['入账日期']=df03['入账日期\\t'].str.replace('\\t','')\n",
    "df03['日期']=pd.to_datetime(df03['入账日期'],format=\"%Y-%m-%d\",errors='coerce')#日期格式转化\n",
    "df03['对方户名']=df03['对方单位\\t'].str.replace('\\t','')\n",
    "df03['收入金额']=df03['转入金额\\t'].str.replace('\\t','')\n",
    "df03['支出金额']=df03['转出金额\\t'].str.replace('\\t','')\n",
    "df03['摘要']=df03['摘要\\t'].str.replace('\\t','')\n",
    "#df03 = df03.fillna(0)#将NAN值变成0\n",
    "#df03[['收入金额', '支出金额']] = df03[['收入金额', '支出金额']].astype(float)\n",
    "##df03['金额']=df03['收入金额']+df03['支出金额']*-1\n",
    "df03['银行']='广东工行 8573'\n",
    "df03=df03[[\"日期\",\"对方户名\",\"摘要\",\"支出金额\",\"收入金额\",\"银行\"]]\n",
    "\n",
    "df04 = pd.read_excel(file4,sheet_name=0,header = 7) #导入登记表，如果加上index_col=2，就是以二级分类作为索引\n",
    "df04['日期']=pd.to_datetime(df04['交易时间'],format=\"%Y-%m-%d\",errors='coerce')#日期格式转化\n",
    "df04 = df04.fillna(0)#将NAN值变成0\n",
    "df04['摘要'] = df04['摘要'].map(str)+ df04['附言'].map(str)##两列合并成一列\n",
    "df04.rename(columns={\"支出\":'支出金额'},inplace=True)#改列名\n",
    "df04.rename(columns={\"收入\":'收入金额'},inplace=True)#改列名\n",
    "df04['银行']=\"广东广发 0156\"\n",
    "df04=df04[[\"日期\",\"对方户名\",\"摘要\",\"支出金额\",\"收入金额\",\"银行\"]]\n",
    "\n",
    "df05 = pd.read_excel(file5,sheet_name=0,header = 0)\n",
    "df05['日期']=pd.to_datetime(df05['交易时间'],format=\"%Y-%m-%d\",errors='coerce')#日期格式转化\n",
    "df05.rename(columns={\"借方发生额（支取）\":'支出金额'},inplace=True)#改列名\n",
    "df05.rename(columns={\"贷方发生额（收入）\":'收入金额'},inplace=True)#改列名\n",
    "df05.rename(columns={\"摘要\":'摘要1'},inplace=True)#改列名\n",
    "df05.rename(columns={\"备注\":'摘要'},inplace=True)\n",
    "df05['银行']=\"广东建行1054\"\n",
    "df05=df05[[\"日期\",\"对方户名\",\"摘要\",\"支出金额\",\"收入金额\",\"银行\"]]\n",
    "\n",
    "df06 = pd.read_excel(file6,sheet_name=0,header = 8) #导入登记表，如果加上index_col=2，就是以二级分类作为索引\n",
    "df06.rename(columns={'交易日期[ Transaction Date ]':'日期'},inplace=True)\n",
    "df06.rename(columns={\"付款人名称[ Payer's Name ]\":'付款人'},inplace=True)\n",
    "df06.rename(columns={\"收款人名称[ Payee's Name ]\":'收款人'},inplace=True)\n",
    "df06.rename(columns={\"交易附言[ Remark ]\":'摘要'},inplace=True)\n",
    "df06.rename(columns={\"交易金额[ Trade Amount ]\":'金额'},inplace=True)\n",
    "df06=df06[[\"日期\",\"付款人\",\"收款人\",\"摘要\",\"金额\"]]\n",
    "df06['对方户名']=df06['付款人'].map(str)+\"付\"+df06['收款人'].map(str)#合并成一列，然后中间加上一个付字\n",
    "df06['日期']=pd.to_datetime(df06['日期'],format=\"%Y%m%d\",errors='coerce')#日期格式转化\n",
    "df06 = df06.fillna(0)#将NAN值变成0\n",
    "df116=df06.loc[df06['金额'] >= 0]\n",
    "df116.rename(columns={'金额':'收入金额'},inplace=True)\n",
    "df126=df06.loc[df06['金额'] < 0]\n",
    "df126.rename(columns={'金额':'支出金额'},inplace=True)\n",
    "df166 = pd.merge(df116, df126, on=['日期', '对方户名','摘要'], how='outer')\n",
    "df166['银行']='湖北中行2986'\n",
    "df166['支出金额']=df166['支出金额']*-1\n",
    "df166 = df166.fillna(0)#将NAN值变成0\n",
    "df166=df166[[\"日期\",\"对方户名\",\"摘要\",\"支出金额\",\"收入金额\",\"银行\"]]\n",
    "\n",
    "df103 = pd.read_csv(file13,header = 1,encoding=\"gb18030\")  #导入登记表，如果加上index_col=2，就是以二级分类作为索引 gb2312 gb18030\n",
    "df103['入账日期']=df103['入账日期\\t'].str.replace('\\t','')\n",
    "df103['日期']=pd.to_datetime(df103['入账日期'],format=\"%Y-%m-%d\",errors='coerce')#日期格式转化\n",
    "df103['对方户名']=df103['对方单位\\t'].str.replace('\\t','')\n",
    "df103['收入金额']=df103['转入金额\\t'].str.replace('\\t','')\n",
    "df103['支出金额']=df103['转出金额\\t'].str.replace('\\t','')\n",
    "df103['摘要']=df103['摘要\\t'].str.replace('\\t','')\n",
    "#df03 = df03.fillna(0)#将NAN值变成0\n",
    "#df03[['收入金额', '支出金额']] = df03[['收入金额', '支出金额']].astype(float)\n",
    "##df03['金额']=df03['收入金额']+df03['支出金额']*-1\n",
    "df103['银行']='湖北工行9666'\n",
    "df103=df103[[\"日期\",\"对方户名\",\"摘要\",\"支出金额\",\"收入金额\",\"银行\"]]\n",
    "\n",
    "df102 = pd.read_excel(file12,sheet_name=0,header = 1) #导入登记表，如果加上index_col=2，就是以二级分类作为索引\n",
    "df102['日期']=pd.to_datetime(df102['会计日期'],format=\"%Y%m%d\",errors='coerce')#日期格式转化\n",
    "##df02['金额']=df02['收入金额']+df02['支出金额']*-1\n",
    "df102.rename(columns={\"交易用途\":'摘要'},inplace=True)\n",
    "df102=df102[[\"日期\",\"对方户名\",\"摘要\",\"支出金额\",\"收入金额\"]]\n",
    "df102 = df102.fillna(0)#将NAN值变成0\n",
    "df102=df102.loc[df102['摘要'] != 0]\n",
    "df102['银行']='广东农行4659'\n",
    "\n",
    "df104 = pd.read_excel(file14,sheet_name=0,header = 2) #导入登记表，如果加上index_col=2，就是以二级分类作为索引\n",
    "df104['日期']=pd.to_datetime(df104['交易日期'],format=\"%Y%m%d\",errors='coerce')#日期格式转化\n",
    "df104.rename(columns={\"支出\":'支出金额'},inplace=True)\n",
    "df104.rename(columns={\"收入\":'收入金额'},inplace=True)\n",
    "df104.rename(columns={\"对方名称\":'对方户名'},inplace=True)#改列名 \n",
    "df104 = df104.fillna(0)#将NAN值变成0\n",
    "df104=df104[[\"日期\",\"对方户名\",\"摘要\",\"支出金额\",\"收入金额\"]]\n",
    "df104['银行']='广东农商行7295'\n",
    "\n",
    "df20 = pd.concat([df16,df02,df03,df04,df05,df166,df103,df102,df104])\n",
    "df20 = df20.sort_values(by='日期')\n",
    "df20['绝对支出金额']=df20['支出金额']\n",
    "df20['绝对收入金额']=df20['收入金额']\n",
    "df20['绝对支出金额'].replace(\"-\",'0')##将-改为0\n",
    "df20['绝对收入金额'].replace(\"-\",'0')\n",
    "\n",
    "file7='重要各银行流水合并.xlsx'\n",
    "with pd.ExcelWriter(file7, mode='a',engine='openpyxl') as writer:\n",
    "    df20.to_excel(writer,sheet_name='I202101',index=False)  #新开一工作表I01而保存文件"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
